using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace Access数据库编辑工具
{
    public partial class Form1 : Form
    {
        private string excelFilePath = string.Empty;
        private string accessFilePath = string.Empty;
        private DataTable excelDataTable = new DataTable();
        private DataTable accessTableSchema = new DataTable();

        public Form1()
        {
            InitializeComponent();
        }

        private void btnOpenDatabase_Click(object sender, EventArgs e)
        {
            // 打开文件对话框，选择 Access 数据库文件
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                openFileDialog.Filter = "Access Database Files (*.mdb, *.accdb)|*.mdb;*.accdb";
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    accessFilePath = openFileDialog.FileName;
                    LoadAccessTables();
                }
            }
        }
        private void LoadAccessTables()
        {
            cmbAccessTables.Items.Clear();
            dataGridViewAccess.DataSource = null;

            if (string.IsNullOrEmpty(accessFilePath))
            {
                MessageBox.Show("请先选择 Access 数据库文件！");
                return;
            }

            try
            {
                string accessConnectionString = GetAccessConnectionString(accessFilePath, txtPassword.Text);
                using (OleDbConnection accessConnection = new OleDbConnection(accessConnectionString))
                {
                    accessConnection.Open();
                    DataTable schemaTable = accessConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    foreach (DataRow row in schemaTable.Rows)
                    {
                        string tableName = row["TABLE_NAME"].ToString();
                        if (!tableName.StartsWith("MSys")) // 过滤系统表
                        {
                            cmbAccessTables.Items.Add(tableName);
                        }
                    }

                    if (cmbAccessTables.Items.Count > 0)
                    {
                        cmbAccessTables.SelectedIndex = 0; // 默认选择第一个表
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载 Access 表失败: " + ex.Message);
            }
        }

        private void cmbTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbAccessTables.SelectedItem == null)
            {
                return;
            }

            string tableName = cmbAccessTables.SelectedItem.ToString();
            LoadAccessTableSchema(tableName);
        }

        private void LoadAccessTableSchema(string tableName)
        {
            try
            {
                string accessConnectionString = GetAccessConnectionString(accessFilePath, txtPassword.Text);
                using (OleDbConnection accessConnection = new OleDbConnection(accessConnectionString))
                {
                    accessConnection.Open();
                    using (OleDbCommand schemaCommand = new OleDbCommand($"SELECT * FROM [{tableName}]", accessConnection))
                    {
                        using (OleDbDataAdapter schemaAdapter = new OleDbDataAdapter(schemaCommand))
                        {
                            accessTableSchema.Clear();
                            schemaAdapter.FillSchema(accessTableSchema, SchemaType.Mapped);

                            DataTable dataTable = new DataTable();
                            schemaAdapter.Fill(dataTable);
                            if (chbJg.Checked)
                            {
                                dataGridViewAccess.DataSource = accessTableSchema; // 显示 Access 表结构
                            }
                            else
                            {
                                dataGridViewAccess.DataSource = dataTable;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载 Access 表结构失败: " + ex.Message);
            }
        }

        private string GetAccessConnectionString(string filePath, string password)
        {
            if (filePath.EndsWith(".mdb"))
            {
                return $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Jet OLEDB:Database Password={password};";
            }
            else if (filePath.EndsWith(".accdb"))
            {
                return $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Jet OLEDB:Database Password={password};";
            }
            else
            {
                throw new NotSupportedException("不支持的数据库文件格式。");
            }
        }
        private string GetExcelConnectionString(string filePath)
        {
            if (filePath.EndsWith(".xls"))
            {
                return $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={filePath};Extended Properties='Excel 8.0;HDR=YES;'";
            }
            else if (filePath.EndsWith(".xlsx"))
            {
                return $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filePath};Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            }
            else
            {
                throw new NotSupportedException("不支持的 Excel 文件格式。");
            }
        }

        private void btnOpenExcel_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog openFileDialog = new OpenFileDialog())
            {
                openFileDialog.Filter = "Excel Files (*.xlsx, *.xls)|*.xlsx;*.xls";
                if (openFileDialog.ShowDialog() == DialogResult.OK)
                {
                    excelFilePath = openFileDialog.FileName;
                    LoadExcelData();
                }
            }
        }
        private void LoadExcelData()
        {
            try
            {
                string excelConnectionString = GetExcelConnectionString(excelFilePath);
                using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString))
                {
                    excelConnection.Open();
                    DataTable schemaTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString(); // 获取第一个工作表

                    using (OleDbCommand excelCommand = new OleDbCommand($"SELECT * FROM [{sheetName}]", excelConnection))
                    {
                        using (OleDbDataAdapter excelAdapter = new OleDbDataAdapter(excelCommand))
                        {
                            excelDataTable.Clear();
                            excelAdapter.Fill(excelDataTable);
                            dataGridViewExcel.DataSource = excelDataTable; // 显示 Excel 数据
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("加载 Excel 文件失败: " + ex.Message);
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (excelDataTable.Rows.Count == 0 || accessTableSchema.Columns.Count == 0)
            {
                MessageBox.Show("请先加载 Excel 文件和 Access 表结构！");
                return;
            }

            try
            {
                string accessConnectionString = GetAccessConnectionString(accessFilePath, txtPassword.Text);
                using (OleDbConnection accessConnection = new OleDbConnection(accessConnectionString))
                {
                    accessConnection.Open();
                    string tableName = cmbAccessTables.SelectedItem.ToString();

                    // 构建 INSERT 语句
                    string columns = string.Join(", ", excelDataTable.Columns.Cast<DataColumn>().Select(c => c.ColumnName));
                    string parameters = string.Join(", ", excelDataTable.Columns.Cast<DataColumn>().Select(c => $"@{c.ColumnName}"));
                    string insertQuery = $"INSERT INTO [{tableName}] ({columns}) VALUES ({parameters})";

                    using (OleDbCommand insertCommand = new OleDbCommand(insertQuery, accessConnection))
                    {
                        // 添加参数
                        foreach (DataColumn column in excelDataTable.Columns)
                        {
                            insertCommand.Parameters.Add($"@{column.ColumnName}", column.DataType);
                        }

                        // 遍历 Excel 数据并插入到 Access 表
                        foreach (DataRow excelRow in excelDataTable.Rows)
                        {
                            foreach (DataColumn column in excelDataTable.Columns)
                            {
                                insertCommand.Parameters[$"@{column.ColumnName}"].Value = excelRow[column.ColumnName];
                            }
                            insertCommand.ExecuteNonQuery();
                        }
                    }
                }

                MessageBox.Show("数据导入成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入数据失败: " + ex.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(accessFilePath))
            {
                MessageBox.Show("请先选择 Access 数据库文件！");
                return;
            }

            string sql = textBox1.Text.Trim();
            if (string.IsNullOrEmpty(sql))
            {
                MessageBox.Show("请输入 SQL 语句！");
                return;
            }

            try
            {
                string accessConnectionString = GetAccessConnectionString(accessFilePath, txtPassword.Text);
                using (OleDbConnection connection = new OleDbConnection(accessConnectionString))
                {
                    connection.Open();

                    if (sql.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase))
                    {
                        // 执行 SELECT 查询
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);
                            dataGridViewAccess.DataSource = dataTable; // 显示查询结果
                            MessageBox.Show($"查询成功，返回 {dataTable.Rows.Count} 行数据。");
                        }
                    }
                    else if (sql.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase) ||
                             sql.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) ||
                             sql.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
                    {
                        // 执行 INSERT、UPDATE 或 DELETE 操作
                        using (OleDbCommand command = new OleDbCommand(sql, connection))
                        {
                            int rowsAffected = command.ExecuteNonQuery();
                            MessageBox.Show($"操作成功，受影响的行数: {rowsAffected}");
                        }
                    }
                    else
                    {
                        MessageBox.Show("不支持的 SQL 语句类型。");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("执行 SQL 失败: " + ex.Message);
            }
        }
    }
}
